﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;
using System.Windows.Forms;

namespace TP2
{
    class DBWorkClass
    {
        OracleConnection connection;
        public void InsertPhysicalClient(string Pasport,string Fio, int Period,DateTime startDate,DateTime berthDate, int SelectedIndex,int Deposit, Double Procent, int Income)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            OracleCommand command = connection.CreateCommand();
            OracleTransaction transaction;
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                LogicClass logicObj = new LogicClass();

                int period = Period;
                string procent = this.TypeConvert(Procent);
                string finish_date = logicObj.GetFinishDate(startDate, period);
                string start_date = startDate.Day.ToString() + "," + startDate.Month.ToString() + "," + startDate.Year.ToString();
                string berth_date = berthDate.Day.ToString() + "," + berthDate.Month.ToString() + "," + berthDate.Year.ToString();
                if (SelectedIndex == 0)
                {
                    int credit = logicObj.GetCreditSumm(Deposit, Procent);
                    int payment = logicObj.GetPaymentMonth(credit, period);
                    bool test_client = logicObj.ClientTest(payment, Income);
                    
                    if (test_client == true)
                    {

                        command.CommandText = "insert into PhysicalClient values(0,'" + Fio + "','" + berth_date + "','" + Pasport + "'," + Income + ")";
                        command.ExecuteNonQuery();
                        command.CommandText = "insert into Loan values(seqphysicalclient.currval,null,null,seqLoan.nextval," + procent + ",'" + start_date + "'," + finish_date + "," + credit.ToString() + "," + payment.ToString() + ")";
                        command.ExecuteNonQuery();
                        transaction.Commit();
                        conClass.CloseConnection(connection);
                    }
                    else { MessageBox.Show("Бомжам кредиты не даём!"); }
                }
                if (SelectedIndex == 1)
                {

                    command.CommandText = "insert into PhysicalClient values(0,'" + Fio + "','" + berth_date + "','" + Pasport + "'," + Income + ")";
                    command.ExecuteNonQuery();
                    command.CommandText = "insert into Accumulation values(seqphysicalclient.currval,null,seqAccumulation.nextval," + procent + ",'" + start_date + "'," + (finish_date) + "," + Deposit + ",'" + start_date.ToString() + "')";
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    conClass.CloseConnection(connection);
                }
            }
            catch (Exception)
            {
                transaction.Rollback();
                conClass.CloseConnection(connection);
            }
        }

        public HistoryTypeClass SelectFromHistory(int id)
        {
            HistoryTypeClass rezultMass = new HistoryTypeClass();
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string select = "select * from history_translation " ;
            OracleCommand selectCommand = new OracleCommand(select, connection);
            OracleDataReader reader = selectCommand.ExecuteReader();
            while (reader.Read())
            {
                rezultMass.id_translation.Add(reader.GetValue(0));
                rezultMass.date.Add(reader.GetValue(1));
                rezultMass.sender_title.Add(reader.GetValue(2));
                rezultMass.recipient_title.Add(reader.GetValue(3));
                rezultMass.deposit.Add(reader.GetValue(4));
                rezultMass.sender_inn.Add(reader.GetValue(5));
                rezultMass.recipient_inn.Add(reader.GetValue(6));
            }
            conClass.CloseConnection(connection);
            return rezultMass;
        }

        public ClassRateType SelectFromAccumulationRate(string client_type)
        {
            ClassConnection obClassCon = new ClassConnection();
            connection = obClassCon.GetConnection();
            ClassRateType result = new ClassRateType();
            string select = "select * from Accumulation_rate where client_type='" + client_type + "'";
            OracleCommand selectClient = new OracleCommand(select, connection);
            OracleDataReader reader = selectClient.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {

                result.id_rate.Add(reader.GetValue(0));
                result.title.Add(reader.GetValue(1));
                result.procent.Add(reader.GetValue(2));
                result.period.Add(reader.GetValue(3));
                i++;
            }      
            obClassCon.CloseConnection(connection);
            return result;
        }

        public ClassRateType SelectFromCreditRate(string client_type)
        {
            ClassConnection obClassCon = new ClassConnection();
            connection = obClassCon.GetConnection();
            ClassRateType result = new ClassRateType();
            string select = "select * from credit_rate where client_type='" + client_type + "'";
            OracleCommand selectClient = new OracleCommand(select, connection);
            OracleDataReader reader = selectClient.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {

                result.id_rate.Add(reader.GetValue(0));
                result.title.Add(reader.GetValue(1));
                result.procent.Add(reader.GetValue(2));
                result.period.Add(reader.GetValue(3));
                i++;
            }
            obClassCon.CloseConnection(connection);
            return result;
        }

        public PhysicalClientTypeClass SelectFromPhysicalClient()
        {
            ClassConnection obClassCon = new ClassConnection();
            connection = obClassCon.GetConnection();
            PhysicalClientTypeClass resulMass = new PhysicalClientTypeClass();
            OracleCommand selectClient = new OracleCommand("select * from PhysicalClient", connection);
            OracleDataReader reader = selectClient.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {

                resulMass.idClient.Add(reader.GetValue(0));
                resulMass.fio.Add(reader.GetValue(1));
                resulMass.date.Add(reader.GetValue(2));
                resulMass.pasport.Add(reader.GetValue(3));
                resulMass.income.Add(reader.GetValue(4));
                i++;
            }

            obClassCon.CloseConnection(connection);
            return resulMass;
        }

        

        public LegalClientTypeClass SelectFromLegalClient()
        {
            ClassConnection obClassCon = new ClassConnection();
            connection = obClassCon.GetConnection();
            LegalClientTypeClass resulMass = new LegalClientTypeClass();
            OracleCommand selectClient = new OracleCommand("select * from LegalClient", connection);
            OracleDataReader reader = selectClient.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {
                resulMass.idClient.Add(reader.GetValue(0));
                resulMass.INN.Add(reader.GetValue(1));
                resulMass.Capital.Add(reader.GetValue(2));
                resulMass.Type.Add(reader.GetValue(3));
                resulMass.Founders.Add(reader.GetValue(4));
                resulMass.Title.Add(reader.GetValue(5));
            }
            obClassCon.CloseConnection(connection);
            return resulMass;
        }

        public void DeleteFromLegalClient(int id_client)
        {
            ClassConnection obClassCon = new ClassConnection();
            connection = obClassCon.GetConnection();
            DialogResult result;
            OracleCommand command = connection.CreateCommand();
            OracleTransaction transaction;
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                result = MessageBox.Show("может не будем его мочить?! Удалить?", "слышь чо", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
                if (result == DialogResult.Yes)
                {
                    command.CommandText = "delete from Accumulation where idUClient=" + id_client;
                    command.ExecuteNonQuery();
                    command.CommandText = "delete from Loan where idUClient=" + id_client;
                    command.ExecuteNonQuery();
                    command.CommandText = "delete from Payments where idUClient="+id_client;
                    command.ExecuteNonQuery();
                    command.CommandText = "delete  from LegalClient where idUClient=" + id_client;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    obClassCon.CloseConnection(connection);
                }
            }
            catch (Exception) { transaction.Rollback(); MessageBox.Show("удалить не удалось!"); obClassCon.CloseConnection(connection); }
        }


        public void DeleteFromPhisycalClient(int id_client)
        {
            ClassConnection obClassCon = new ClassConnection();
            connection = obClassCon.GetConnection();
            OracleCommand command = connection.CreateCommand();
            OracleTransaction transaction;
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                DialogResult result;
                result = MessageBox.Show("может не будем его мочить?! Удалить?", "слышь чо", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
                if (result == DialogResult.Yes)
                {
                    command.CommandText = "delete from Accumulation where idPClient=" + id_client.ToString();
                    command.ExecuteNonQuery();
                    command.CommandText = "delete from Loan where idPClient=" + id_client.ToString();
                    command.ExecuteNonQuery();
                    command.CommandText = "delete  from PhysicalClient where idPClient=" + id_client.ToString();
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    obClassCon.CloseConnection(connection);
                }
            }
            catch (Exception)
            {
                transaction.Rollback();
                obClassCon.CloseConnection(connection);
                MessageBox.Show("удалить не удалось!");
            }          
        }

        public void EditPhysicalClient(string Fio, DateTime Date,string Pasport,string Incom, int id_client)
        {
            try
            {
                ClassConnection conClass = new ClassConnection();
                string update;
                connection = conClass.GetConnection();               
                update = "update PhysicalClient  set FIO='" + Fio + "',Data='" + Date.Day.ToString() + "," + Date.Month.ToString() + "," + Date.Year.ToString() + "',Pasport ='" + Pasport + "',Income =" + Incom + " where idPClient=" + id_client.ToString();
                OracleCommand update_command = new OracleCommand(update, connection);
                update_command.ExecuteNonQuery();
                conClass.CloseConnection(connection);
                
                
            }
            catch
            {
                MessageBox.Show("ошибка во входных данных");
            }
        }

        public AccumulationTypeClass SelectFromAccumulation(string client_type, int id_client)
        {
            ClassConnection conClass = new ClassConnection();
            AccumulationTypeClass resultMass = new AccumulationTypeClass();
            connection = conClass.GetConnection();
            string select;
            if (client_type.Equals("физический"))
            {
                select = "select * from Accumulation where idPClient=" + id_client.ToString();
            }
            else
            {
                select = "select * from Accumulation where idUClient=" + id_client.ToString();
            }
            OracleCommand selectCom = new OracleCommand(select, connection);
            OracleDataReader reader = selectCom.ExecuteReader();
            while (reader.Read())
            {
                resultMass.number.Add(reader.GetValue(2));
                resultMass.proc.Add(reader.GetValue(3));
                resultMass.start_date.Add(reader.GetValue(4));
                resultMass.finish_date.Add(reader.GetValue(5));
                resultMass.deposit.Add(reader.GetValue(6));
            }
            
            conClass.CloseConnection(connection);
            return resultMass;
        }

        public LoanTypeClass SelectFromLoan(string client_type, int id_client)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            LoanTypeClass resultMass = new LoanTypeClass();
            string select="";
            switch (client_type)
            {
                case "физический":
                    select = "select * from Loan where idPClient=" + id_client.ToString();
                    break;
                case "юридический":
                    select = "select * from Loan where idUClient=" + id_client.ToString();
                    break;
                case"банк":
                    select = "select * from Loan where idBank=" + id_client.ToString();
                    break;
            }            
            OracleCommand selectCom = new OracleCommand(select, connection);
            OracleDataReader reader = selectCom.ExecuteReader();
            while (reader.Read())
            {
                resultMass.number.Add(reader.GetValue(3));
                resultMass.procent.Add(reader.GetValue(4));
                resultMass.start_date.Add(reader.GetValue(5));
                resultMass.finish_date.Add(reader.GetValue(6));
                resultMass.credit.Add(reader.GetValue(7));
                resultMass.payment.Add(reader.GetValue(8));
            }
            conClass.CloseConnection(connection);
            return resultMass;
        }

        public int SelectFromPayments(int id_client)
        {
            int credit=0;
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string select = "select Credit from Payments where idUClient="+id_client.ToString();
            OracleCommand comSelect = new OracleCommand(select, connection);
            OracleDataReader reader = comSelect.ExecuteReader();
            while (reader.Read())
            {
                credit =Convert.ToInt32( reader.GetValue(0));
            }
            conClass.CloseConnection(connection);
            return credit;
        }

        public void DeleteFromAccumulation(int number)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string com = "delete Accumulation where Namber=" + number.ToString();
            DialogResult result;
            result = MessageBox.Show("счёт не закрыт! Удалить?", "слышь", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
            if (result == DialogResult.Yes)
            {
                OracleCommand commandUpdate = new OracleCommand(com, connection);
                commandUpdate.ExecuteNonQuery();
            }
            
            conClass.CloseConnection(connection);
        }

        public void DeleteFromRate(int id, int SelectedIndex)
        {
            DialogResult result;
            result = MessageBox.Show("может не будем его мочить?! Удалить?", "слышь чо", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
            if (result == DialogResult.Yes)
            {
                ClassConnection obClassCon = new ClassConnection();
                connection = obClassCon.GetConnection();
                string deletQuery;
                if (SelectedIndex == 0)
                {
                    deletQuery = "delete from credit_rate where id_rate=" + id;
                }
                else
                {
                    deletQuery = "delete from Accumulation_rate where id_rate=" + id;
                }
                OracleCommand delCom = new OracleCommand(deletQuery, connection);
                delCom.ExecuteNonQuery();
                obClassCon.CloseConnection(connection);
            }
        }

        public void InsertRate(string title, Double procent,int period, string client, int tab_index)
        {
            try
            {
                ClassConnection conObj = new ClassConnection();
                connection = conObj.GetConnection();              
                string command;
                if (tab_index == 0)
                {
                    string proc = this.TypeConvert(procent);
                    command = "insert into credit_rate values(0,'" + title + "'," + proc + "," + period.ToString() + ",'" + client + "')";
                }
                else 
                {
                    string proc = this.TypeConvert(procent);
                    command = "insert into Accumulation_rate values(0,'" + title + "'," + proc + "," + period.ToString() + ",'" + client + "')";
                }
                OracleCommand insert = new OracleCommand(command, connection);
                insert.ExecuteNonQuery();
                conObj.CloseConnection(connection);
                
            }
            catch
            {
                MessageBox.Show("ошибка во входных данных");
            }
        }

        public Int64 SelectFromCorrespondent(int idBank)
        {
            Int64 Deposit=0;
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string select="select * from Correspondent where idBank="+idBank.ToString();
            OracleCommand selectCom = new OracleCommand(select, connection);
            OracleDataReader reader = selectCom.ExecuteReader();
            while (reader.Read())
            {
                Deposit = Convert.ToInt64(reader.GetValue(2));
            }
            conClass.CloseConnection(connection);
            return Deposit;
        }

        public void UpdateFromCorrespondent(int idBank,Int64 Deposit)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string update = "update Correspondent set Credit =" + Deposit.ToString() + " where idBank="+idBank.ToString();
            OracleCommand updateCom = new OracleCommand(update, connection);
            updateCom.ExecuteNonQuery();
            conClass.CloseConnection(connection);

        }


        public BankTypeClass SelectFromBank()
        {
            BankTypeClass masResult = new BankTypeClass();
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string select = "select * from Bank";
            OracleCommand comSelect = new OracleCommand(select, connection);
            OracleDataReader reader = comSelect.ExecuteReader();
            while (reader.Read())
            {
                masResult.id_bank.Add(reader.GetValue(0));
                masResult.title.Add(reader.GetValue(1));
                masResult.bik.Add(reader.GetValue(2));
                masResult.inn.Add(reader.GetValue(3));
                masResult.capital.Add(reader.GetValue(4));
                masResult.type.Add(reader.GetValue(5));
                masResult.founder.Add(reader.GetValue(6));
            }
            conClass.CloseConnection(connection);
            return masResult;
        }

        public void InsertFromBank( string Title, string Bik, string Inn, long Capital, string Type, string Founders, int period, Double Procent, int Credit, DateTime startDate)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            OracleCommand insCom = connection.CreateCommand();
            OracleTransaction transaction;
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                LogicClass logicObj = new LogicClass();
                string procent = this.TypeConvert(Procent);
                string finish_date = logicObj.GetFinishDate(startDate, period);
                string start_date = startDate.Day.ToString() + "," + startDate.Month.ToString() + "," + startDate.Year.ToString();
                int credit = logicObj.GetCreditSumm(Credit, Procent);
                int payment = logicObj.GetPaymentMonth(credit, period);
                Capital = Capital + Credit;
                insCom.CommandText = "insert into Bank values(0,'" + Title + "','" + Bik + "','" + Inn + "'," + Capital.ToString() + ",'" + Type + "','" + Founders + "')";
                insCom.ExecuteNonQuery();
                insCom.CommandText = "insert into Loan values(null,null,seqBank.currval,seqLoan.nextval," + procent + ",'" + start_date + "'," + finish_date + "," + credit.ToString() + "," + payment.ToString() + ")";
                insCom.ExecuteNonQuery();
                insCom.CommandText = "insert into Correspondent values(seqBank.currval,0," + Capital.ToString() + ")";
                insCom.ExecuteNonQuery();
                transaction.Commit();
                //OracleCommand insCom = new OracleCommand(insert, connection);
                insCom.ExecuteNonQuery();
                conClass.CloseConnection(connection);
            }
            catch (FormatException) { transaction.Rollback(); conClass.CloseConnection(connection); }
            
        }

        public void UpdateFromBank(int idBank, string Title, string Bik, string Inn, long Capital, string Type, string Founders)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string insert = "update  Bank set Title='" + Title + "',BIK ='" + Bik + "',INN ='" + Inn + "',Capital =" + Capital.ToString() + ",Type= '" + Type + "',Founders='" + Founders + "' where idBank=" + idBank.ToString();
            OracleCommand insCom = new OracleCommand(insert, connection);
            insCom.ExecuteNonQuery();
            conClass.CloseConnection(connection);
        }

        public void DeleteFromBank(int idBank)
        {
            DialogResult result;
            result = MessageBox.Show("может не будем его мочить?! Удалить?", "слышь чо", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
            if (result == DialogResult.Yes)
            {
                ClassConnection classCon = new ClassConnection();
                connection = classCon.GetConnection();
                OracleTransaction transaction;
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                OracleCommand comDelete = connection.CreateCommand();
                try
                {
                    comDelete.CommandText = "delete from Loan where idBank =" + idBank.ToString();
                    comDelete.ExecuteNonQuery();
                    comDelete.CommandText = "delete from Correspondent where idBank =" + idBank.ToString();
                    comDelete.ExecuteNonQuery();
                    comDelete.CommandText = "delete from Bank where idBank=" + idBank.ToString();
                    comDelete.ExecuteNonQuery();
                    transaction.Commit();
                    classCon.CloseConnection(connection);
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    classCon.CloseConnection(connection);
                    MessageBox.Show("удалить не удалось");
                }
            }
        }

        private string TypeConvert(Double value)
        {
            value = Math.Round(value, 2);
            string tmp = value.ToString();

            string result = tmp.Replace(',', '.');
            return result;
        }

        public void InsertToAccumulation(string client_type,DateTime Date,int id_client,int Period,Double Procent, int Deposit)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string date = Date.Day.ToString() + "," + Date.Month.ToString() + "," + Date.Year.ToString();        
            int period;
            period = Period;
            string procent=this.TypeConvert(Procent);
            LogicClass logic = new LogicClass();
            string finish_date = logic.GetFinishDate(Date, period);
            string insertAccumulation = "";
            switch (client_type)
            {
                case "физический":
                    insertAccumulation = "insert into Accumulation values(" + id_client.ToString() + ",null,seqAccumulation.nextval," + procent + ",'" + date.ToString() + "'," + (finish_date) + "," + Deposit.ToString() + ",'" + date.ToString() + "')";
                    break;
                case "юридический":
                    insertAccumulation = "insert into Accumulation values(null," + id_client.ToString() + ",seqAccumulation.nextval," + procent + ",'" + date.ToString() + "'," + (finish_date) + "," + Deposit.ToString() + ",'" + date.ToString() + "')";
                    break;
                case "банк":
                    break;
            }

            OracleCommand comIsAccumulation = new OracleCommand(insertAccumulation, connection);
            comIsAccumulation.ExecuteNonQuery();
            conClass.CloseConnection(connection);
        }

        public void InsertFromLoan(DateTime Date, int period, int Credit,Double Procent, string client_type, int id_client)
        {
           

            string date = Date.Day.ToString() + "," + Date.Month.ToString() + "," + Date.Year.ToString();
            LogicClass logic = new LogicClass();
            //int index, period, payment;
            string procent = this.TypeConvert(Procent);
            string finish_date = logic.GetFinishDate(Date, period);
            int credit = logic.GetCreditSumm(Credit, Procent);
            int payment = logic.GetPaymentMonth(credit, period);
            string insertLoan = "";
            switch (client_type)
            {
                case "физический":
                    insertLoan = "insert into Loan  values(" + id_client.ToString() + ",null,null,seqLoan.nextval," + procent + ",'" + date.ToString() + "'," + (finish_date) + "," + credit.ToString() + "," + payment.ToString() + ")";
                    break;
                case "юридический":
                    insertLoan = "insert into Loan  values(null," + id_client.ToString() + ",null,seqLoan.nextval," + procent + ",'" + date.ToString() + "'," + (finish_date) + "," + credit.ToString() + "," + payment.ToString() + ")";
                    break;
                case "банк":
                    Int64 Deposit = this.SelectFromCorrespondent(id_client);
                    Deposit = Deposit + Credit;
                    this.UpdateFromCorrespondent(id_client, Deposit);
                    insertLoan = "insert into Loan  values(null,null," + id_client.ToString() + ",seqLoan.nextval," + procent + ",'" + date.ToString() + "'," + (finish_date) + "," + credit.ToString() + "," + payment.ToString() + ")";
                    break;
            }
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            OracleCommand comIsAccumulation = new OracleCommand(insertLoan, connection);
            comIsAccumulation.ExecuteNonQuery();
            conClass.CloseConnection(connection);
        }

        public void InsertFromLegalClient(DateTime date, int SelectedIndex, int period,int Deposit,Double Procent,int Capital, string INN,string organization_type,string founder, string title)
        {
            try
            {
                ClassConnection conClass = new ClassConnection();
                connection = conClass.GetConnection();
                OracleCommand command = connection.CreateCommand();
                OracleTransaction transaction;
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    LogicClass logicObj = new LogicClass();
                    string procent = this.TypeConvert(Procent);
                    string finish_date = logicObj.GetFinishDate(date, period);
                    string start_date = date.Day.ToString() + "," + date.Month.ToString() + "," + date.Year.ToString();
                    if (SelectedIndex == 0)
                    {
                        int credit = logicObj.GetCreditSumm(Deposit, Procent);
                        int payment = logicObj.GetPaymentMonth(credit, period);
                        bool test_client = logicObj.ClientTest(payment, Capital);
                        if (test_client == true)
                        {
                            //string date = dateTimePicker1.Value.Day.ToString() + "," + dateTimePicker1.Value.Month.ToString() + "," + dateTimePicker1.Value.Year.ToString();
                            command.CommandText = "insert into LegalClient values(0,'" + INN + "'," + Capital.ToString() + ",'" + organization_type + "','" + founder + "','" + title + "')";
                            command.ExecuteNonQuery();
                            command.CommandText = "insert into Loan values(null,seqLegalClient.currval,null,seqLoan.nextval," + procent + ",'" + start_date + "'," + finish_date + "," + credit.ToString() + "," + payment.ToString() + ")";
                            command.ExecuteNonQuery();
                            command.CommandText="insert into Payments values(seqLegalClient.currval,0,0)";
                            command.ExecuteNonQuery();
                            transaction.Commit();
                            conClass.CloseConnection(connection);
                        }
                        else { MessageBox.Show("Бомжам кредиты не даём!"); }
                    }
                    if (SelectedIndex == 1)
                    {
                        //string date = dateTimePicker1.Value.Day.ToString() + "," + dateTimePicker1.Value.Month.ToString() + "," + dateTimePicker1.Value.Year.ToString();
                        command.CommandText = "insert into LegalClient values(0,'" + INN + "'," + Capital.ToString() + ",'" + organization_type + "','" + founder + "','" + title + "')";
                        command.ExecuteNonQuery();
                        command.CommandText = "insert into Accumulation values(null,seqLegalClient.currval,seqAccumulation.nextval," + procent + ",'" + start_date + "'," + (finish_date) + "," + Deposit.ToString() + ",'" + start_date.ToString() + "')";
                        command.ExecuteNonQuery();
                        command.CommandText = "insert into Payments values(seqLegalClient.currval,0,0)";
                        command.ExecuteNonQuery();
                        transaction.Commit();
                        conClass.CloseConnection(connection);
                    }
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    conClass.CloseConnection(connection);
                }
            }
            catch (FormatException) { MessageBox.Show("чё вы там во входных данных накосячили!"); }
            catch (OracleException) { MessageBox.Show("сломалась!"); }

        }

        public void UpdateFromPayments(int id_client, int credit)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string update = "update Payments set Credit=" + credit.ToString() + " where idUClient="+id_client.ToString();
            OracleCommand updateCommand = new OracleCommand(update, connection);
            updateCommand.ExecuteNonQuery();
            conClass.CloseConnection(connection);
        }

        public void UpdateLegalClient(string Title,string INN,string Founders,int Capital,string OrgType,int idClient)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string update = "update LegalClient set INN='"+INN+"',Capital="+Capital.ToString()+",Type='"+OrgType+"',Founders='"+Founders+"',Title='"+Title+"' where idUClient="+idClient.ToString();
            OracleCommand updateCommand = new OracleCommand(update, connection);
            updateCommand.ExecuteNonQuery();
        }

        public void InsertFromHistory(string senderTitle,string senderInn, string recipientTitle, string recipientInn, int Deposit)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            string date = System.DateTime.Now.Day.ToString() + "," + System.DateTime.Now.Month.ToString() + "," + System.DateTime.Now.Year.ToString();
            string insert = "insert into history_translation values(0,'"+date+"','"+senderTitle+"','"+recipientTitle+"',"+Deposit.ToString()+",'"+senderInn+"','"+recipientInn+"')";
            OracleCommand comInsert = new OracleCommand(insert, connection);
            comInsert.ExecuteNonQuery();
            conClass.CloseConnection(connection);
        }

        public void UpdateAccumulationDeposit()
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            AccumulationTypeClass selMass = new AccumulationTypeClass();
            string select = "select * from Accumulation";
            OracleCommand selectCommand = new OracleCommand(select, connection);
            OracleDataReader reader = selectCommand.ExecuteReader();
            while (reader.Read())
            {
                selMass.number.Add(reader.GetValue(2));
                selMass.proc.Add(reader.GetValue(3));
                selMass.start_date.Add(reader.GetValue(4));
                selMass.finish_date.Add(reader.GetValue(5));
                selMass.deposit.Add(reader.GetValue(6));
                selMass.update_date.Add(reader.GetValue(7));
            }
            int i = 0;
            LogicClass logicObj = new LogicClass();
            while (i < selMass.number.Count)
            {
                DateTime update = Convert.ToDateTime(selMass.update_date[i]);
                int period = (System.DateTime.Now.Month - update.Month) + (System.DateTime.Now.Year - update.Year) * 12;
                if (period > 0)
                {
                    selMass.deposit[i] = logicObj.UpdateDeposit(Convert.ToInt32(selMass.deposit[i]), period, Convert.ToInt32(selMass.proc[i]));
                    string query = "update Accumulation set Deposit=" + selMass.deposit[i].ToString() + ",UpdateDate='" + Convert.ToString(System.DateTime.Now.Day) + Convert.ToString(System.DateTime.Now.Month) + Convert.ToString(System.DateTime.Now.Year) + "' where Namber=" + Convert.ToString(selMass.number[i]);
                    OracleCommand updateCommand = new OracleCommand(query, connection);
                    updateCommand.ExecuteNonQuery();
                }
                i++;
            }

            conClass.CloseConnection(connection);
        }

        public void UpdateAccumulation(int deposit, bool flag, int period, DateTime date, int number, Double Procent)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();
            
            string com = "";
            if ((deposit >= 0) && (flag == false))
            {
                com = "update Accumulation set Deposit=" + deposit.ToString() + "where Namber=" + number.ToString();
                OracleCommand commandUpdate = new OracleCommand(com, connection);
                commandUpdate.ExecuteNonQuery();
            }
            if ((deposit > 0) && (flag == true))
            {
                LogicClass logic = new LogicClass();
                string procent = this.TypeConvert(Procent);
                string start_date = "'" + date.Day.ToString() + "," + date.Month.ToString() + "," + date.Year.ToString() + "'";
                string finish_date = logic.GetFinishDate(date, period);
                com = "update Accumulation set Procent=" + procent + ",StartDate=" + start_date + ",FinishDate=" + finish_date + ",Deposit=" + deposit.ToString() + "where Namber=" + number.ToString();
                OracleCommand commandUpdate = new OracleCommand(com, connection);
                commandUpdate.ExecuteNonQuery();
            }
            if (deposit < 0) { MessageBox.Show("это ограбление?!"); }
            conClass.CloseConnection(connection);
        }

        public void UpdateLoan(int vznos, int credit, DateTime finish_date, int number)
        {
            ClassConnection conClass = new ClassConnection();
            connection = conClass.GetConnection();          
            if ((credit - vznos) >= 0) { credit = credit - vznos; }
            else { MessageBox.Show("к чему такая щедрость?))"); }
            LogicClass logicObj = new LogicClass();
            int period = (finish_date.Year - System.DateTime.Now.Year) * 12 + (finish_date.Month - System.DateTime.Now.Month);
            int payment = logicObj.GetPaymentMonth(credit, period);
            string update = "update Loan set Credit=" + credit.ToString() + ",Payment=" + payment.ToString() + "where Namber=" + number.ToString();
            OracleCommand updateCommand = new OracleCommand(update, connection);
            updateCommand.ExecuteNonQuery();
            conClass.CloseConnection(connection);
        }

        public void UpdateRate(string title,Double Procent, int period, string client_type, int id_rate, int tab_index)
        {
            try
            {
                ClassConnection conClass = new ClassConnection();
                connection = conClass.GetConnection();
                string update;
                string procent = this.TypeConvert(Procent);
                if (tab_index == 0)
                {
                    update = "update credit_rate set title='" + title + "',procent=" + procent.ToString() + ",period=" + period.ToString() + ",client_type='" + client_type + "' where id_rate=" + id_rate.ToString();
                }
                else { update = "update Accumulation_rate set title='" + title + "',procent=" + procent + ",period=" + period.ToString() + ",client_type='" + client_type + "' where id_rate=" + id_rate.ToString(); }
                OracleCommand update_command = new OracleCommand(update, connection);
                update_command.ExecuteNonQuery();
                conClass.CloseConnection(connection);
                
            }
            catch(FormatException)
            {
                MessageBox.Show("ошибка во входных данных");
            }
        }

        
    }
}
